package com.crm.dao.purchaseDAO;

import com.crm.pojo.Purchaseproductiondetail;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface PurchaseproductiondetailDAO extends CrudRepository<Purchaseproductiondetail,Long>, JpaSpecificationExecutor<Purchaseproductiondetail> {
    //查询所有采购id为xx的采购产品明细
    @Query("from Purchaseproductiondetail ppd where ppd.pidfk=?1 and ppd.purchaseproductiondetailIsDel != '是'")
    public List<Purchaseproductiondetail> findAllByPid(Long pid);
    //查询采购id为xxx的采购产品明细数量
    @Query(nativeQuery = true,value = "select count(*) from PURCHASEPRODUCTIONDETAIL ppd where ppd.PIDFK=?1 and ppd.IS_DEL!='是'")
    public Long queryCountP(Long pid);
    //通过产品proid查询采购状态为采购完成-已入库下的未删除以及未退回的采购产品明细中的采购总数
    @Query(nativeQuery = true,value = "select sum(ppd.PPCOUNT) from PURCHASEPRODUCTIONDETAIL ppd join PURCHASE p on ppd.PIDFK=p.PURID join PRODUCTION_DETAILS pd on pd.PID=ppd.PROID where (ppd.IS_DEL='否' or ppd.IS_DEL='待退货' or ppd.IS_DEL='已删除')  and pd.PRODUCTION_DETAILS_IS_DEL='未删除'and p.PURSTATUS='采购完成-已入库'and pd.PID=?1")
    public Long queryCountByproid(Long proid);
}
